WHENEVER SQLERROR EXIT FAILURE ROLLBACK
SET SERVEROUTPUT ON SIZE 1000000;

DECLARE
/*
	Description:	Upload employee's assignment information
	Name:			Crystal
	Date:			2006/2/3
*/
	l_new_person_id		crst_fnd_per_all_people_map.person_id_n%type;
	data_error			EXCEPTION;
	update_error		EXCEPTION;

   	CURSOR cur_fnd_assign IS
   		SELECT assg.*
   		FROM crst_fnd_assignment assg
   		--WHERE person_id=340
   		ORDER BY person_id;

BEGIN
   	FOR rec IN cur_fnd_assign LOOP
	 	BEGIN
	 		SELECT person_id_n
	 		INTO l_new_person_id
	 		FROM crst_fnd_per_all_people_map
	 		WHERE person_id = rec.person_id;
		EXCEPTION
			WHEN OTHERS THEN
				RAISE data_error;
	 	END;
		--dbms_output.put_line(to_char(l_new_person_id));
	 	BEGIN
			UPDATE 	per_all_assignments_f
			SET		APPLICANT_RANK=rec.APPLICANT_RANK,
					APPLICATION_ID=rec.APPLICATION_ID,
					ASS_ATTRIBUTE_CATEGORY=rec.ASS_ATTRIBUTE_CATEGORY,
					ASS_ATTRIBUTE1=rec.ASS_ATTRIBUTE1,
					ASS_ATTRIBUTE10=rec.ASS_ATTRIBUTE10,
					ASS_ATTRIBUTE11=rec.ASS_ATTRIBUTE11,
					ASS_ATTRIBUTE12=rec.ASS_ATTRIBUTE12,
					ASS_ATTRIBUTE13=rec.ASS_ATTRIBUTE13,
					ASS_ATTRIBUTE14=rec.ASS_ATTRIBUTE14,
					ASS_ATTRIBUTE15=rec.ASS_ATTRIBUTE15,
					ASS_ATTRIBUTE16=rec.ASS_ATTRIBUTE16,
					ASS_ATTRIBUTE17=rec.ASS_ATTRIBUTE17,
					ASS_ATTRIBUTE18=rec.ASS_ATTRIBUTE18,
					ASS_ATTRIBUTE19=rec.ASS_ATTRIBUTE19,
					ASS_ATTRIBUTE2=rec.ASS_ATTRIBUTE2,
					ASS_ATTRIBUTE20=rec.ASS_ATTRIBUTE20,
					ASS_ATTRIBUTE21=rec.ASS_ATTRIBUTE21,
					ASS_ATTRIBUTE22=rec.ASS_ATTRIBUTE22,
					ASS_ATTRIBUTE23=rec.ASS_ATTRIBUTE23,
					ASS_ATTRIBUTE24=rec.ASS_ATTRIBUTE24,
					ASS_ATTRIBUTE25=rec.ASS_ATTRIBUTE25,
					ASS_ATTRIBUTE26=rec.ASS_ATTRIBUTE26,
					ASS_ATTRIBUTE27=rec.ASS_ATTRIBUTE27,
					ASS_ATTRIBUTE28=rec.ASS_ATTRIBUTE28,
					ASS_ATTRIBUTE29=rec.ASS_ATTRIBUTE29,
					ASS_ATTRIBUTE3=rec.ASS_ATTRIBUTE3,
					ASS_ATTRIBUTE30=rec.ASS_ATTRIBUTE30,
					ASS_ATTRIBUTE4=rec.ASS_ATTRIBUTE4,
					ASS_ATTRIBUTE5=rec.ASS_ATTRIBUTE5,
					ASS_ATTRIBUTE6=rec.ASS_ATTRIBUTE6,
					ASS_ATTRIBUTE7=rec.ASS_ATTRIBUTE7,
					ASS_ATTRIBUTE8=rec.ASS_ATTRIBUTE8,
					ASS_ATTRIBUTE9=rec.ASS_ATTRIBUTE9,
					ASSIGNMENT_CATEGORY= rec.ASSIGNMENT_CATEGORY,
					ASSIGNMENT_NUMBER=rec.ASSIGNMENT_NUMBER,
					ASSIGNMENT_SEQUENCE=rec.ASSIGNMENT_SEQUENCE,
					ASSIGNMENT_STATUS_TYPE_ID=rec.ASSIGNMENT_STATUS_TYPE_ID,
					ASSIGNMENT_TYPE=rec.ASSIGNMENT_TYPE,
					BARGAINING_UNIT_CODE=rec.BARGAINING_UNIT_CODE,
					BUSINESS_GROUP_ID=CAPBRIMP_MAPPING.GetBGMapping(rec.BUSINESS_GROUP_ID),
					CAGR_GRADE_DEF_ID=rec.CAGR_GRADE_DEF_ID,
					CAGR_ID_FLEX_NUM=rec.CAGR_ID_FLEX_NUM,
					CHANGE_REASON=rec.CHANGE_REASON,
					COLLECTIVE_AGREEMENT_ID=rec.COLLECTIVE_AGREEMENT_ID,
					COMMENT_ID=rec.COMMENT_ID,
					CONTRACT_ID=rec.CONTRACT_ID,
					DATE_PROBATION_END=rec.DATE_PROBATION_END,
					DEFAULT_CODE_COMB_ID=rec.DEFAULT_CODE_COMB_ID,
					EFFECTIVE_END_DATE=rec.EFFECTIVE_END_DATE,
					EFFECTIVE_START_DATE=rec.EFFECTIVE_START_DATE,
					EMPLOYEE_CATEGORY=rec.EMPLOYEE_CATEGORY,
					EMPLOYMENT_CATEGORY=rec.EMPLOYMENT_CATEGORY,
					ESTABLISHMENT_ID=rec.ESTABLISHMENT_ID,
					FREQUENCY=rec.FREQUENCY,
					GRADE_ID=rec.GRADE_ID,
					HOURLY_SALARIED_CODE=rec.HOURLY_SALARIED_CODE,
					INTERNAL_ADDRESS_LINE=rec.INTERNAL_ADDRESS_LINE,
					JOB_ID=CAPPOIMP_MAPPING.GetJOBIDMapping(rec.JOB_ID),
					JOB_POST_SOURCE_NAME=rec.JOB_POST_SOURCE_NAME,
					LABOUR_UNION_MEMBER_FLAG=rec.LABOUR_UNION_MEMBER_FLAG,
					LOCATION_ID=rec.LOCATION_ID,
					MANAGER_FLAG=rec.MANAGER_FLAG,
					NORMAL_HOURS=rec.NORMAL_HOURS,
					NOTICE_PERIOD=rec.NOTICE_PERIOD,
					NOTICE_PERIOD_UOM=rec.NOTICE_PERIOD_UOM,
					OBJECT_VERSION_NUMBER=rec.OBJECT_VERSION_NUMBER,
					ORGANIZATION_ID=CAPBRIMP_MAPPING.GetBGMapping(rec.ORGANIZATION_ID),
					PAY_BASIS_ID=rec.PAY_BASIS_ID,
					PAYROLL_ID=rec.PAYROLL_ID,
					PEOPLE_GROUP_ID=rec.PEOPLE_GROUP_ID,
					PERF_REVIEW_PERIOD=rec.PERF_REVIEW_PERIOD,
					PERF_REVIEW_PERIOD_FREQUENCY=rec.PERF_REVIEW_PERIOD_FREQUENCY,
					PERIOD_OF_PLACEMENT_DATE_START=rec.PERIOD_OF_PLACEMENT_DATE_START,
					PERIOD_OF_SERVICE_ID=rec.PERIOD_OF_SERVICE_ID,
					PERSON_REFERRED_BY_ID=rec.PERSON_REFERRED_BY_ID,
					POSITION_ID=CAPPOIMP_MAPPING.GetPositionIDMapping(rec.POSITION_ID),
					POSTING_CONTENT_ID=rec.POSTING_CONTENT_ID,
					PRIMARY_FLAG=rec.PRIMARY_FLAG,
					PROBATION_PERIOD=rec.PROBATION_PERIOD,
					PROBATION_UNIT=rec.PROBATION_UNIT,
					PROGRAM_APPLICATION_ID=rec.PROGRAM_APPLICATION_ID,
					PROGRAM_ID=rec.PROGRAM_ID,
					PROGRAM_UPDATE_DATE=rec.PROGRAM_UPDATE_DATE,
					PROJECT_TITLE=rec.PROJECT_TITLE,
					RECRUITER_ID=rec.RECRUITER_ID,
					RECRUITMENT_ACTIVITY_ID=rec.RECRUITMENT_ACTIVITY_ID,
					REQUEST_ID=rec.REQUEST_ID,
					SAL_REVIEW_PERIOD=rec.SAL_REVIEW_PERIOD,
					SAL_REVIEW_PERIOD_FREQUENCY=rec.SAL_REVIEW_PERIOD_FREQUENCY,
					SET_OF_BOOKS_ID=CAPBRIMP_MAPPING.GetSOBIDMapping(rec.SET_OF_BOOKS_ID),
					SOFT_CODING_KEYFLEX_ID=rec.SOFT_CODING_KEYFLEX_ID,
					SOURCE_ORGANIZATION_ID=rec.SOURCE_ORGANIZATION_ID,
					SOURCE_TYPE=rec.SOURCE_TYPE,
					SPECIAL_CEILING_STEP_ID=rec.SPECIAL_CEILING_STEP_ID,
					SUPERVISOR_ID=CAPBRIMP_MAPPING.GetEmployeeMapping(rec.SUPERVISOR_ID),
					TIME_NORMAL_FINISH=rec.TIME_NORMAL_FINISH,
					TIME_NORMAL_START=rec.TIME_NORMAL_START,
					TITLE=rec.TITLE,
					VACANCY_ID=rec.VACANCY_ID,
					VENDOR_ASSIGNMENT_NUMBER=rec.VENDOR_ASSIGNMENT_NUMBER,
					VENDOR_EMPLOYEE_NUMBER=rec.VENDOR_EMPLOYEE_NUMBER,
					VENDOR_ID=rec.VENDOR_ID,
					WORK_AT_HOME=rec.WORK_AT_HOME
			WHERE person_id = l_new_person_id;

		EXCEPTION
			WHEN OTHERS THEN
				dbms_output.put_line (SQLERRM);
				raise update_error;
		END;
	 END LOOP;
	 COMMIT;
	 dbms_output.put_line ('Commit is completed');
EXCEPTION
	WHEN data_error THEN
		dbms_output.put_line ('Data Error');
		ROLLBACK;
	WHEN update_error THEN
		dbms_output.put_line ('Updated Error');
		ROLLBACK;
	WHEN OTHERS THEN
		dbms_output.put_line (SQLERRM);
		ROLLBACK;
END;
/